﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace program1
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SQLite;


        public class ClassRepository
        {
            private string connectionString;

            public ClassRepository(string connectionString)
            {
                this.connectionString = connectionString;
            }

        public void InsertClass(int schoolId, string name, string teacher)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand("INSERT INTO Classes (SchoolID, Name, Teacher) VALUES (@SchoolID, @Name, @Teacher)", connection))
                {
                    command.Parameters.AddWithValue("@SchoolID", schoolId);
                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Teacher", teacher);

                    command.ExecuteNonQuery();
                }
            }
        }

        public void DeleteClass(int classID)
            {
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    using (SQLiteCommand command = new SQLiteCommand("DELETE FROM Classes WHERE ClassID = @ClassID", connection))
                    {
                        command.Parameters.AddWithValue("@ClassID", classID);
                        command.ExecuteNonQuery();
                    }
                }
            }

        public void UpdateClass(int classId, string name, string teacher)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand("UPDATE Classes SET Name = @Name, Teacher = @Teacher WHERE ClassID = @ClassID", connection))
                {
                    command.Parameters.AddWithValue("@Name", name);
                    command.Parameters.AddWithValue("@Teacher", teacher);
                    command.Parameters.AddWithValue("@ClassID", classId);

                    command.ExecuteNonQuery();
                }
            }
        }

        public List<Class> GetClassesBySchool(int schoolID)
            {
                List<Class> classes = new List<Class>();

                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Classes WHERE SchoolID = @SchoolID", connection))
                    {
                        command.Parameters.AddWithValue("@SchoolID", schoolID);
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Class classObj = new Class
                                {
                                    ClassID = Convert.ToInt32(reader["ClassID"]),
                                    SchoolID = Convert.ToInt32(reader["SchoolID"]),
                                    Name = reader["Name"].ToString(),
                                    Teacher = reader["Teacher"].ToString()
                                };
                                classes.Add(classObj);
                            }
                        }
                    }
                }

                return classes;
            }

            public List<Class> GetAllClasses()
            {
                List<Class> classes = new List<Class>();

                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Classes", connection))
                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Class classObj = new Class
                            {
                                ClassID = Convert.ToInt32(reader["ClassID"]),
                                SchoolID = Convert.ToInt32(reader["SchoolID"]),
                                Name = reader["Name"].ToString(),
                                Teacher = reader["Teacher"].ToString()
                            };
                            classes.Add(classObj);
                        }
                    }
                }

                return classes;
            }

        public Class GetClassById(int classId)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                connection.Open();

                using (SQLiteCommand command = new SQLiteCommand("SELECT * FROM Classes WHERE ClassID = @ClassID", connection))
                {
                    command.Parameters.AddWithValue("@ClassID", classId);

                    using (SQLiteDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            // 从数据库中读取班级信息
                            int id = reader.GetInt32(reader.GetOrdinal("ClassID"));
                            int schoolId = reader.GetInt32(reader.GetOrdinal("SchoolID"));
                            string name = reader.GetString(reader.GetOrdinal("Name"));
                            string teacher = reader.GetString(reader.GetOrdinal("Teacher"));

                            // 创建 Class 对象并返回
                            Class classInfo = new Class
                            {
                                ClassID = id,
                                SchoolID = schoolId,
                                Name = name,
                                Teacher = teacher
                            };

                            return classInfo;
                        }
                    }
                }
            }

            return null; // 如果未找到班级，返回 null
        }
    }
 }



